"""
⑩ 按照性别来进行人员分组如下显示（group by + group_concat()）；
        | 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
	| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
	| 中性   | 金星                                                       |
	| 保密   | 凤姐                                                       |
"""
import pymysql
from pymysql.cursors import DictCursor

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='6666',
    database='homework'
)
try:
    cursor=conn.cursor(DictCursor)
    sql = """
        select * from student
    """
    ret_num = cursor.execute(sql)
    result = cursor.fetchall()
    # print(result)

    m = []
    f = []
    n = []
    s = []
    for stu in result:
        if stu['Sex']== '男':
            m.append(stu['Name'])
        elif stu['sex']=='女':
            f.append(stu['name'])
        elif stu['sex'] == '中性':
            n.append(stu['name'])
        else:
            s.append(stu['name'])

    print('| 男     | ',end='')
    for i in m:
        print(i,end=',')

    print('| 女     | ', end='')
    for i in f:
        print(i, end=',')

    print('| 女性     | ', end='')
    for i in f:
        print(i, end=',')

    print('| 保密     | ', end='')
    for i in f:
        print(i, end=',')

except:
    conn.rollback()


